<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>APSW SQLite query explainer</title>
  <style>
  * {
    box-sizing: border-box;
  }

  body {
    font-family: Helvetica, Arial, sans-serif;
    line-height: 1.6;
    margin: 0;
    padding: 20px;
    background: #f5f5f5;
  }

  .container {
    max-width: 1200px;
    margin: 0 auto;
    background: white;
    padding: 20px;
    border-radius: 8px;
    box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
  }

  h1 {
    margin-top: 0;
    color: #333;
  }

  .query-area {
    margin-bottom: 20px;
  }

  textarea {
    width: 100%;
    min-height: 150px;
    padding: 12px;
    border: 1px solid #ccc;
    border-radius: 4px;
    font-size: 16px;
    font-family: monospace;
    resize: vertical;
  }

  button {
    background: #0066cc;
    color: white;
    border: none;
    padding: 12px 24px;
    border-radius: 4px;
    cursor: pointer;
    font-size: 16px;
  }

  button:hover {
    background: #0052a3;
  }

  button:disabled {
    background: #cccccc;
    cursor: not-allowed;
  }

  .results {
    margin-top: 20px;
    border: 1px solid #eee;
    border-radius: 4px;
    padding: 20px;
  }

  .error {
    color: #cc0000;
    padding: 12px;
    background: #fff0f0;
    border-radius: 4px;
    margin-top: 20px;
  }

  .loading {
    display: none;
    margin-top: 20px;
    color: #666;
  }

  pre {
    white-space: pre-wrap;
    word-wrap: break-word;
    background: #f8f8f8;
    padding: 12px;
    border-radius: 4px;
    overflow-x: auto;
  }

  details {
    margin-bottom: 20px;
    padding: 10px;
    background: #f8f8f8;
    border-radius: 4px;
  }

  summary {
    cursor: pointer;
    padding: 8px 0;
    color: #444;
  }

  #initialSql {
    margin-top: 10px;
  }

  .param-container {
    margin-top: 10px;
  }

  .param-row {
    display: flex;
    align-items: center;
    gap: 8px;
    margin-bottom: 8px;
  }

  .param-input {
    flex: 1;
    padding: 8px;
    border: 1px solid #ccc;
    border-radius: 4px;
    font-size: 16px;
  }

  .icon-button {
    background: none;
    border: none;
    padding: 8px;
    cursor: pointer;
    color: #666;
    font-size: 20px;
    line-height: 1;
    display: flex;
    align-items: center;
    justify-content: center;
  }

  .icon-button:hover {
    background: none;
    color: #000;
  }

  .param-label {
    min-width: 30px;
    font-size: 14px;
    color: #666;
  }
  </style>
</head>
<body>
  <div class="container">
    <h1>APSW SQLite query explainer</h1>
    
    <details>
      <summary>Initial SQL</summary>
      <textarea id="initialSql" placeholder="Enter setup SQL to run before main query (e.g. CREATE TABLE statements)..."></textarea>
    </details>

    <div class="query-area">
      <textarea id="sqlQuery" placeholder="Enter your SQL query here...">SELECT * FROM sqlite_master;</textarea>
      
      <div id="params" class="param-container">
        <div class="param-row" data-param="1">
          <span class="param-label">p1</span>
          <input type="text" class="param-input" placeholder="Parameter value">
          <button class="icon-button add-param" title="Add parameter">+</button>
        </div>
      </div>

      <button id="executeBtn">Execute query</button>
    </div>

    <div id="loading" class="loading">
      Executing query...
    </div>

    <div id="results" class="results">
      <pre id="output">Results will appear here</pre>
    </div>
  </div>

  <script type="module">
const sqlQuery = document.getElementById('sqlQuery')
const initialSql = document.getElementById('initialSql')
const executeBtn = document.getElementById('executeBtn')
const loading = document.getElementById('loading')
const output = document.getElementById('output')
const paramsContainer = document.getElementById('params')

function addParam() {
  const rows = paramsContainer.querySelectorAll('.param-row')
  const newNum = rows.length + 1
  const newRow = createParamRow(newNum)
  paramsContainer.appendChild(newRow)
  updateParamNumbers()
}

// Add click event listeners to all add parameter buttons
document.querySelectorAll('.add-param').forEach(button => {
  button.addEventListener('click', addParam)
})

function createParamRow(num) {
  const row = document.createElement('div')
  row.className = 'param-row'
  row.dataset.param = num

  const label = document.createElement('span')
  label.className = 'param-label'
  label.textContent = `p${num}`

  const input = document.createElement('input')
  input.type = 'text'
  input.className = 'param-input'
  input.placeholder = 'Parameter value'

  const addBtn = document.createElement('button')
  addBtn.className = 'icon-button add-param'
  addBtn.title = 'Add parameter'
  addBtn.textContent = '+'
  addBtn.addEventListener('click', addParam)

  if (num > 1) {
    const removeBtn = document.createElement('button')
    removeBtn.className = 'icon-button remove-param'
    removeBtn.title = 'Remove parameter'
    removeBtn.textContent = '−'
    removeBtn.onclick = () => {
      row.remove()
      updateParamNumbers()
    }
    row.appendChild(label)
    row.appendChild(input)
    row.appendChild(removeBtn)
    row.appendChild(addBtn)
  } else {
    row.appendChild(label)
    row.appendChild(input)
    row.appendChild(addBtn)
  }

  return row
}

function updateParamNumbers() {
  const rows = paramsContainer.querySelectorAll('.param-row')
  rows.forEach((row, index) => {
    const num = index + 1
    row.dataset.param = num
    row.querySelector('.param-label').textContent = `p${num}`
  })
}

async function initPyodide() {
  loading.style.display = 'block'
  executeBtn.disabled = true
  
  try {
    let pyodide = await loadPyodide()
    await pyodide.loadPackage('micropip')
    const micropip = pyodide.pyimport('micropip')
    await micropip.install('apsw')
    
    return pyodide
  } catch (err) {
    showError('Failed to initialize Python environment: ' + err.message)
    throw err
  }
}

function showError(message) {
  output.innerHTML = `<div class="error">${message}</div>`
}

function getParameters() {
  const params = []
  const rows = paramsContainer.querySelectorAll('.param-row')
  rows.forEach(row => {
    const value = row.querySelector('.param-input').value.trim()
    if (value) {
      params.push(value)
    }
  })
  return params
}

async function executeQuery(pyodide, sql, setupSql, params) {
  try {
    const result = await pyodide.runPythonAsync(`
import apsw
import apsw.ext
import json

db = apsw.Connection(":memory:")

# Execute setup SQL if provided
if """${setupSql}""".strip():
    db.cursor().execute("""${setupSql}""")

# Convert parameters to a list
params = json.loads("""${JSON.stringify(params)}""")

from dataclasses import is_dataclass
import inspect

def to_serializable(obj):
    """
    Recursively convert an object to a JSON-serializable format.
    Handles nested dictionaries, lists, and dataclasses.
    """
    # Handle None
    if obj is None:
        return None
        
    # Handle basic types that are already JSON serializable
    if isinstance(obj, (str, int, float, bool)):
        return obj
        
    # Handle dataclass objects
    if is_dataclass(obj):
        return {
            field.name: to_serializable(getattr(obj, field.name))
            for field in getattr(obj, '__dataclass_fields__', {}).values()
        }
        
    # Handle dictionaries
    if isinstance(obj, dict):
        return {
            str(key): to_serializable(value)
            for key, value in obj.items()
        }
        
    # Handle lists and tuples
    if isinstance(obj, (list, tuple)):
        return [to_serializable(item) for item in obj]
        
    # Handle objects with __dict__ attribute
    if hasattr(obj, '__dict__'):
        return to_serializable(vars(obj))
        
    # For any other type, convert to string
    try:
        json.dumps(obj)
        return obj
    except (TypeError, OverflowError):
        return str(obj)

details = apsw.ext.query_info(db, """${sql}""", params, actions=True, expanded_sql=True, explain=True, explain_query_plan=True)

# Convert to serializable format and dump to JSON
result = to_serializable(details)
json.dumps(result)
    `)

    output.textContent = JSON.stringify(JSON.parse(result), null, 2)
  } catch (err) {
    showError('Query execution failed: ' + err.message)
    throw err
  } finally {
    loading.style.display = 'none'
    executeBtn.disabled = false
  }
}

let pyodideInstance = null

executeBtn.addEventListener('click', async () => {
  const sql = sqlQuery.value.trim()
  const setupSql = initialSql.value.trim()
  const params = getParameters()
  
  if (!sql) {
    showError('Please enter a SQL query')
    return
  }

  try {
    if (!pyodideInstance) {
      pyodideInstance = await initPyodide()
    }
    await executeQuery(pyodideInstance, sql, setupSql, params)
  } catch (err) {
    console.error('Execution failed:', err)
  }
})
</script>
<script src="https://cdn.jsdelivr.net/pyodide/v0.27.2/full/pyodide.js"></script>
</body>
</html>